SQL Joins: Inner Join, Left join, Right Join, And Full Outer Join

SQL Server Joins: Definition, Types and Examples

A join is a way to combine data from two or more tables into a single result set based on a related column between the tables. Joining tables allows you to retrieve data that is spread across multiple tables in your database and combine it into a single view.

To perform a join, you must specify the related columns between the tables in the ON clause of the SQL statement. The columns used in the ON clause should be of the same data type and contain corresponding values. The result of the join is a new table that contains all the columns from the original tables where the related columns match.

TYPES OF JOINS :

• Inner join : An inner join returns only the rows that have matching values in both tables. It essentially creates a new table with only the rows that have matching values in the specified columns of both tables.returns only the rows where there is a match between the related columns in both tables.

SYNTAX

SELECT *

FROM table1

INNER JOIN table2

ON table1.column = table2.column;

• Full Outer join: A full outer join returns all the rows from both tables and includes NULL values where there is no match between the related columns. This is useful when you want to retrieve all the data from both tables, regardless of whether there is a match between them.

SYNTAX

SELECT *

FROM table1

FULL OUTER JOIN table2

ON table1.column = table2.column;

• Left join: A left join returns all the rows from the left table and the matching rows from the right table. If there is no match, the result will contain NULL values for the missing data.

SYNTAX

SELECT *

FROM table1

LEFT JOIN table2

ON table1.column = table2.column;

• Right join : A right join returns all the rows from the right table and the matching rows from the left table. If there is no match, the result will contain NULL values for the missing data.

SYNTAX

SELECT *

FROM table1

RIGHT JOIN table2

ON table1.column = table2.column;

TABLES USED IN BELOW EXAMPLES

CREATE TABLE Customer

(cust_ID INT NOT NULL,

Firstname varchar(50),

Address varchar(255),

city varchar(50),

PRIMARY KEY (cust_ID)

);

INSERT INTO CUSTOMER

VALUES (1, ‘ADAM’, ‘RAILWAY COLONY’, ‘PUNE’),

(2, ‘ADJOA’, ‘REA ROAD’, ‘SAAKCON’),

(3, ‘ROBERT’, ‘SANJAY COLONY’, ‘NAGPUR’),

(4,’JUDE’, ‘ASHOKNAGAR’, ‘INDORE’);

CREATE TABLE ORDERS(

ORDERID INT NOT NULL,

ORDERDATE DATE NOT NULL,

cust_ID INT,

PRIMARY KEY (ORDERID),

FOREIGN KEY (cust_ID) REFERENCES customer (cust_ID)

);

INSERT INTO [dbo].[ORDERS]

VALUES (1,’2023-02-01′,1),

(2,’2023-02-02′,2),

(3,’2023-02-03′,1);

EXAMPLES :

1. You have two tables, CUSTOMER and Orders. To get the list of all CUSTOMERS who have placed ORDERS, you can use an inner join.

SELECT customer.Firstname, orders.orderdate

FROM customer

INNER JOIN orders

ON CUSTOMER.cust_ID = orders.cust_id;

RESULT :

2. To get a list of all customers and their orders (if any), you can use left join.

SELECT * FROM CUSTOMER
LEFT JOIN Orders
ON CUSTOMER.cust_ID = Orders.cust_ID;

3.To get a list of all orders and their corresponding customers (if any), you could use a right join.SELECT *FROM CUSTOMERRIGHT JOIN OrdersON CUSTOMER.cust_ID = Orders.cust_ID;
RESULT :

4.To get a list of all customers and all orders (including those with no matching customer), you could use a full outer join.SELECT *FROM CUSTOMERFULL OUTER JOIN OrdersON CUSTOMER.cust_ID = Orders.cust_ID;RESULT :

To join more than two tables together in SQL, you would use the JOIN clause multiple times, connecting the tables one by one.
SYNTAX

SELECT t1.column1, t2.column2, t3.column3

FROM table1 AS t1

JOIN table2 AS t2

ON t1.id = t2.t1_id

JOIN table3 AS t3 ON t2.id = t3.t2_id;

In this example, the SELECT statement is pulling data from three different tables: table1, table2, and table3. We use aliases t1, t2, and t3 for each table to make the SQL statement easier to read.

The JOIN clause is used twice in this statement to connect the three tables. The first JOIN clause connects table1 and table2, and the second JOIN clause connects table2 and table3. Each JOIN clause includes the ON keyword, which specifies the condition that must be met for the tables to be joined.

In this case, we join table1 and table2 on the condition t1.id = t2.t1_id, which means that the id column in table1 must match the t1_id column in table2. Then we join table2 and table3 on the condition t2.id = t3.t2_id, which means that the id column in table2 must match the t2_id column in table3.

The result of this SQL statement will be a table that includes data from all three tables, with columns column1 from table1, column2 from table2, and column3 from table3.

Using aliases can make the SQL statement easier to read, especially when working with multiple tables. By assigning a short alias to each table, you can avoid having to type out the full table name multiple times in the statement.

TABLE 3

CREATE TABLE PRODUCTS(

PRODUCTID INT NOT NULL,

PRODUCTNAME VARCHAR(25) NOT NULL,

cust_ID INT,

PRIMARY KEY (PRODUCTID),

FOREIGN KEY (cust_ID) REFERENCES customer (cust_ID));

INSERT INTO PRODUCTS

VALUES (1,’EDIBLEOIL’,1),

(2,’TEA’,2),

(3,’PULSE’,1);
EXAMPLES : (JOIN of three tables and Filtering data on the basis of orderdate using where clause)

SELECT customer.Firstname, orders.orderdate,PRODUCTS.PRODUCTNAME

FROM customer

INNER JOIN orders ON CUSTOMER.cust_ID = orders.cust_id

INNER JOIN PRODUCTSON CUSTOMER.cust_ID = PRODUCTS.cust_ID

WHERE ORDERDATE = ‘2023-02-03’;
RESULT :

Vijay Kashyap
Learn SQL in simplified manner